package com.yulian.web_api.report;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.repository.CommonRepositoryImpl;
import com.yulian.util.CommonUtils;

import com.yulian.web_api.vo.GoodsGrossProfitCommissionDetailVo;
import com.yulian.web_api.vo.GoodsPriceTrendReVo;
import com.yulian.web_api.vo.GoodsPriceTrendVo;
import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.pgclient.PgPool;

public class GoodsPriceTrendManager {
	final InternalLogger logger = Log4JLoggerFactory.getInstance(GoodsPriceTrendManager.class);
	CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();
	
	/**
     * 创建查询符合搜索条件记录的sql语句 
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSql(JsonObject queryJS, int query_offset, int query_limit) {
    	SelectQuery sq = new SelectQuery(); 
		sq.addCustomColumns(new CustomSql("id"), new CustomSql("prepare_pickup_plan_price_bid_id"),new CustomSql("goods_price_bid_parameter_id"), new CustomSql("sales_pickup_plan_balance_id"),new CustomSql("create_user"),new CustomSql("update_user"),new CustomSql("aggregation_id"),new CustomSql("create_time"))
		    .addCustomFromTable("goods_price_trend");
	   	this.commonRepository.processQueryJsonObject(sq, queryJS);  
		String sql=sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
        logger.info(sql);
        return sql;  
    }
    
    /**
	 * 根据传入的查询参数获取满足条件的记录
	 * @param client 连接池对象
	 * @param config 配置对象
	 * @param bodyJSObj body参数对象
	 * @return 满足条件的记录
	 */
	public Future<JsonObject> getRecords(PgPool client, JsonObject config, JsonObject bodyJSObj) { 
		Promise<JsonObject> promise = Promise.promise(); 
		//最后返回的JsonObject对象
 		JsonObject responseJSObj = new JsonObject();
 		// 验证必须传递的字段
 	 	List<String>  must_fields = Arrays.asList("aggregation_id_list","start_time","end_time");
 	 	JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
 	 	if (must != null) {
 	 		promise.complete(must);  
 	 		return promise.future();
 	 	}
		//从bodyJson中获取数据
		String aggregation_id_list = bodyJSObj.getString("aggregation_id_list"); 
		String start_time = bodyJSObj.getString("start_time"); 
		String end_time = bodyJSObj.getString("end_time");
		String aggregation_id_list2 = aggregation_id_list.substring(1,aggregation_id_list.length()-1);
		List<String> str = List.of(aggregation_id_list2.split(","));
		String new_aggregation_id="";
		for (int i =0  ; i < str.size() ; i++){
			if (i==str.size()-1){
				new_aggregation_id=new_aggregation_id+str.get(i);
			}else {
				new_aggregation_id=new_aggregation_id+str.get(i)+",";
			}
		}


		String finalNew_aggregation_id = new_aggregation_id;
		this.commonRepository.getCon(client).onSuccess(conn -> {
			String sql = " select spppd.settlement_price,spppd.aggregation_id, " +
					" (select ag.aggregation_name from aggregation_goods ag where ag.is_delete=0 and ag.id=spppd.aggregation_id) as aggregation_name, " +
					" sppp.payment_date " +
					"from supplier_pickup_plan_payment_detail spppd  left join supplier_pickup_plan_payment sppp on spppd.supplier_pickup_plan_payment_id=sppp.id " +
					"where sppp.is_delete=0 and spppd.is_delete=0 " +
					"and sppp.payment_date >= '"+start_time+"' " +
					"and sppp.payment_date < '"+end_time+"' ";
			if (!finalNew_aggregation_id.equals("") && finalNew_aggregation_id!=null){
				sql=sql+" and spppd.aggregation_id in ( "+ finalNew_aggregation_id +" ) " ;
			}
			Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsSettlementPrice");

			String sql2 =" select sppc.real_settlement_price,sspppd.aggregation_id, " +
					" (select ag.aggregation_name from aggregation_goods ag where ag.is_delete=0 and ag.id=sspppd.aggregation_id) as aggregation_name, " +
					" ssppp.payment_date,sppc.sales_prepare_pickup_plan_id, " +
					"    (  select ppppb.standard_freight_price from sales_prepare_pickup_plan sppp left join prepare_pickup_plan_price_bid ppppb  on " +
					"    ppppb.aggregation_id=sspppd.aggregation_id and sppp.site_id=ppppb.site_id and sppp.goods_gather_area_id=ppppb.goods_gather_area_id " +
					"     where sppp.is_delete=0  and sppp.id=sppc.sales_prepare_pickup_plan_id  and ppppb.is_delete=0  limit 1  ) as standard_freight_price, " +
					"     (  select gpbp.supplier_profit_rate  from sales_prepare_pickup_plan sppp left join goods_price_bid_parameter gpbp  on " +
					"     gpbp.aggregation_id=sspppd.aggregation_id and sppp.site_id=gpbp.site_id and sppp.goods_gather_area_id=gpbp.goods_gather_area_id " +
					"      where sppp.is_delete=0  and sppp.id=sppc.sales_prepare_pickup_plan_id  and gpbp.is_delete=0  limit 1  ) as supplier_profit_rate, " +
					"      (  select gpbp.maximum_rate  from sales_prepare_pickup_plan sppp left join goods_price_bid_parameter gpbp  on " +
					"     gpbp.aggregation_id=sspppd.aggregation_id and sppp.site_id=gpbp.site_id and sppp.goods_gather_area_id=gpbp.goods_gather_area_id " +
					"     where sppp.is_delete=0  and sppp.id=sppc.sales_prepare_pickup_plan_id  and gpbp.is_delete=0  limit 1  ) as maximum_rate, " +
					"    (  select gpbp.minmum_rate  from sales_prepare_pickup_plan sppp left join goods_price_bid_parameter gpbp  on " +
					"      gpbp.aggregation_id=sspppd.aggregation_id and sppp.site_id=gpbp.site_id and sppp.goods_gather_area_id=gpbp.goods_gather_area_id " +
					"      where sppp.is_delete=0  and sppp.id=sppc.sales_prepare_pickup_plan_id  and gpbp.is_delete=0  limit 1  ) as minmum_rate " +
					"from sales_supplier_pickup_plan_payment_detail sspppd left join sales_supplier_pickup_plan_payment ssppp " +
					"    on sspppd.sales_supplier_pickup_plan_payment_id=ssppp.id " +
					"    left join sales_pickup_plan_command sppc on sspppd.sales_pickup_plan_command_id = sppc.id " +
					"where ssppp.is_delete=0 and sspppd.is_delete=0 and sppc.is_delete=0 " +
					"  and ssppp.payment_date >= '"+start_time+"' " +
					"  and ssppp.payment_date < '"+end_time+"'  ";
			if (!finalNew_aggregation_id.equals("") && finalNew_aggregation_id!=null){
				sql2=sql2+" and sspppd.aggregation_id in ( "+ finalNew_aggregation_id +" ) " ;
			}
			Future<JsonArray> future2 =  this.commonRepository.queryJsonArray(conn, sql2, "getRecordsSalesSettlementPrice");

			CompositeFuture.join(Arrays.asList(future,future2)).onComplete(ar2 -> {
				if (ar2.succeeded()) {//顺利执行多个future
					JsonArray resJSArray=(JsonArray)ar2.result().list().get(0);
					JsonArray resJSArray2=(JsonArray)ar2.result().list().get(1);
					// 最后组装含有name的Json对象和数组
					JsonArray recordsJSArray = new JsonArray();
					List<GoodsPriceTrendVo> list1=new ArrayList<>();
					List<GoodsPriceTrendVo> list1New=new ArrayList<>();
					List<GoodsPriceTrendVo> list2=new ArrayList<>();
					List<GoodsPriceTrendVo> list2New=new ArrayList<>();
					for(int i=0; i<resJSArray.size(); i++) {
						JsonObject record_obj = resJSArray.getJsonObject(i);
						GoodsPriceTrendVo vo1 = new GoodsPriceTrendVo();
						vo1.setSettlementPrice(Double.parseDouble(record_obj.getString("settlement_price")));
						vo1.setAggregationId(Integer.valueOf(record_obj.getString("aggregation_id")));
						vo1.setPaymentDate(record_obj.getString("payment_date"));
						vo1.setAggregationName(record_obj.getString("aggregation_name"));
						list1.add(vo1);
					}
					for(int i=0; i<resJSArray2.size(); i++) {
						JsonObject record_obj = resJSArray2.getJsonObject(i);
						GoodsPriceTrendVo vo1 = new GoodsPriceTrendVo();
						vo1.setRealSettlementPrice(Double.parseDouble(record_obj.getString("real_settlement_price")));
						vo1.setAggregationId(Integer.valueOf(record_obj.getString("aggregation_id")));
						vo1.setPaymentDate(record_obj.getString("payment_date"));
						vo1.setSalesPreparePickupPlanId(Integer.valueOf(record_obj.getString("sales_prepare_pickup_plan_id")));
						vo1.setStandardFreightPrice(Double.parseDouble(record_obj.getString("standard_freight_price")));
						vo1.setSupplierProfitRate(Double.parseDouble(record_obj.getString("supplier_profit_rate")));
						vo1.setMaximumRate(Double.parseDouble(record_obj.getString("maximum_rate")));
						vo1.setMinmumRate(Double.parseDouble(record_obj.getString("minmum_rate")));
						vo1.setAggregationName(record_obj.getString("aggregation_name"));
						list2.add(vo1);
					}
					//过滤不同aggregation_id和不同payment_date下最下settlement_price
					Map<Integer, List<GoodsPriceTrendVo>> collect = list1.stream().collect(Collectors.groupingBy(GoodsPriceTrendVo::getAggregationId)); //同aggregation_id
					collect.forEach((key, value) -> {
						Map<String, List<GoodsPriceTrendVo>> collect2 = value.stream()
								.collect(Collectors.groupingBy(GoodsPriceTrendVo::getPaymentDate));  //同payment_date
						collect2.forEach((key2, value2) -> {
							if(value2.size()==1){
								list1New.add(value2.get(0));
							}else {
								BigDecimal bigDecimal0 = new BigDecimal(value2.get(0).getSettlementPrice());
								int min = 0;
								for(int y = 1 ; y < value2.size(); y++){
									BigDecimal bigDecimal1 = new BigDecimal(value2.get(y).getSettlementPrice());
									if (bigDecimal1.compareTo(bigDecimal0)<0){
										//小于 则记录y的值 循环完了后add该i下标对象
										min=y;
									}
								}
								list1New.add(value2.get(min));
							}
						});
					});

					Map<Integer, List<GoodsPriceTrendVo>> collect3 = list2.stream().collect(Collectors.groupingBy(GoodsPriceTrendVo::getAggregationId)); //同aggregation_id
					collect3.forEach((key, value) -> {
						Map<String, List<GoodsPriceTrendVo>> collect4 = value.stream()
								.collect(Collectors.groupingBy(GoodsPriceTrendVo::getPaymentDate));  //同payment_date
						collect4.forEach((key2, value2) -> {
							if(value2.size()==1){
								list1New.add(value2.get(0));
							}else {
								BigDecimal bigDecimal0 = new BigDecimal(value2.get(0).getRealSettlementPrice());
								int min = 0;
								for(int y = 1 ; y < value2.size(); y++){
									BigDecimal bigDecimal1 = new BigDecimal(value2.get(y).getRealSettlementPrice());
									if (bigDecimal1.compareTo(bigDecimal0)<0){
										//小于 则记录y的值 循环完了后add该i下标对象
										min=y;
									}
								}
								list2New.add(value2.get(min));
							}
						});
					});
					//end过滤最低值完成

					//开始组装返回前端的数据集合
					Map<String,List<GoodsPriceTrendReVo>> listMap = new HashMap<>();
					List<GoodsPriceTrendReVo> reVoList = new ArrayList<>();
						for (int u = 0 ; u < list2New.size(); u++){
							GoodsPriceTrendReVo reVo = new GoodsPriceTrendReVo();
							BigDecimal bigDecimal1 =new BigDecimal(list2New.get(u).getRealSettlementPrice());
							BigDecimal bigDecimal2 =new BigDecimal(list2New.get(u).getStandardFreightPrice());
							BigDecimal bigDecimal3 =new BigDecimal(list2New.get(u).getSupplierProfitRate());
							BigDecimal bigDecimal4 = bigDecimal1.subtract(bigDecimal2);
							reVo.setPrice(bigDecimal4.multiply(bigDecimal3).doubleValue());

							BigDecimal bigDecimal5 =new BigDecimal(list2New.get(u).getMaximumRate());
							BigDecimal bigDecimal6 =new BigDecimal(list2New.get(u).getMinmumRate());
							String str10 = bigDecimal6.multiply(bigDecimal4).doubleValue()+"-"+bigDecimal5.multiply(bigDecimal4).doubleValue();
							reVo.setQujian(str10);
							BigDecimal bigDecimalMax = bigDecimal5.multiply(bigDecimal4);
							Integer aggregationId1 = list2New.get(u).getAggregationId();
							String paymentDate1=list2New.get(u).getPaymentDate();
							reVo.setPaymentDate(paymentDate1);
							reVo.setAggregationName(list2New.get(u).getAggregationName());
							boolean g =false;
							for (int r =0 ; r < list1New.size();r++){
								Integer aggregationId2 = list1New.get(r).getAggregationId();
								String paymentDate2=list1New.get(r).getPaymentDate();
								if (aggregationId1==aggregationId2 && paymentDate1.equals(paymentDate2)){
									//相等则判断状态
									reVo.setSettlementPrice(list1New.get(r).getSettlementPrice());
									BigDecimal bigDecimal0 = new BigDecimal(list1New.get(r).getSettlementPrice());
									if (bigDecimal0.compareTo(bigDecimalMax)<=0){
										reVo.setStatus("合理");
									}else {
										reVo.setStatus("预警");
									}
									g=true;
									break;
								}
							}
							if (!g){
								reVo.setSettlementPrice(0.00);
								reVo.setStatus("合理");
							}
							reVoList.add(reVo);
						}
						//判断完了list2New 但是list1New 集合可能还有单独自己的数据没有处理
					for (int q =0 ; q < list1New.size() ; q++){
						GoodsPriceTrendReVo reVo = new GoodsPriceTrendReVo();
						Integer aggregationId2 = list1New.get(q).getAggregationId();
						String paymentDate2=list1New.get(q).getPaymentDate();
						boolean g =false;
						for (int d = 0 ; d <list2New.size();d++){
							Integer aggregationId1 = list2New.get(d).getAggregationId();
							String paymentDate1=list2New.get(d).getPaymentDate();
							if (aggregationId2==aggregationId1 && paymentDate2.equals(paymentDate1)){
								//相等就ture  过滤
								g=true;
								break;
							}
						}
						if (!g){
							//g==false 需要单独处理的数据 和add
							reVo.setSettlementPrice(list1New.get(q).getSettlementPrice());
							reVo.setPrice(0.00);
							reVo.setQujian("-");
							reVo.setStatus("合理");
							reVo.setPaymentDate(paymentDate2);
							reVo.setAggregationName(list1New.get(q).getAggregationName());
							reVoList.add(reVo);
						}
					}
					Map<String, List<GoodsPriceTrendReVo>> collect10 = reVoList.stream().collect(Collectors.groupingBy(GoodsPriceTrendReVo::getAggregationName)); //同AggregationName
					collect10.forEach((key, value) -> {
						listMap.put(key,value);
					});


					JsonObject dataJSObj = new JsonObject();
					dataJSObj.put("records", listMap);
					responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);

					promise.complete(responseJSObj);
				} else {
					logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
					promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getGoodsPriceTrendRecords", "获取销售地价格趋势失败")) ;
				}
			});

		}).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsPriceTrendRecords", "数据库连接失败")));
	return promise.future(); 
	}       
}
